﻿Imports Entidad
Imports System.Data
Imports System.Data.SqlClient

Public Class DocumentoAlmacenDetallePickeadorUbicacionBandejaDatos

    Function obtenerDatosXBandeja(CodigoBandeja As String, estado As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT dcad.DCI_Codigo AS Pedido, COUNT(*) AS CantItems, pc.PRC_Descripcion  " &
                                  "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                                  "ON dadpu.CIA = dadpub.CIA AND dadpu.SUC_Codigo = dadpub.SUC_Codigo " &
                                  "AND dadpu.DADPU_Codigo = dadpub.DADPU_Codigo AND dadpu.DADPU_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                                  "ON dadp.CIA = dadpu.CIA AND dadp.SUC_Codigo = dadpu.SUC_Codigo " &
                                  "AND dadp.TDOC_Codigo = dadpu.TDOC_Codigo " &
                                  "AND dadp.DCA_Codigo = dadpu.DCA_Codigo AND dadp.DADP_Codigo = dadpu.DADP_Codigo " &
                                  "AND dadp.DADP_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle dad " &
                                  "ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo " &
                                  "AND dad.TDOC_Codigo = dadp.TDOC_Codigo " &
                                  "AND dad.DCA_Codigo = dadp.DCA_Codigo " &
                                  "AND dad.DAD_Codigo = dadp.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                                  "ON dcad.CIA_Almacen = dad.CIA AND dcad.SUC_Almacen = dad.SUC " &
                                  "AND dcad.TDOC_Almacen = dad.TDOC_Codigo " &
                                  "AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo " &
                                  "AND dcad.DCAD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Detalle dcd " &
                                  "ON dcd.CIA = dcad.CIA_Cliente AND dcd.SUC = dcad.SUC_Cliente " &
                                  "AND dcd.TDOC_Codigo = dcad.TDOC_Cliente " &
                                  "AND dcd.DCI_Codigo = dcad.DCI_Codigo AND dcd.DCD_Codigo = dcad.DCD_Codigo " &
                                  "AND dcd.DCD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente dc " &
                                  "ON dc.CIA = dcd.CIA AND dc.SUC_Codigo = dcd.SUC " &
                                  "AND dc.TDOC_Codigo = dcd.TDOC_Codigo AND dc.DCI_Codigo = dcd.DCI_Codigo " &
                                  "AND DCI_Estado = '" & estado & "' " &
                                  "LEFT JOIN Cliente c " &
                                  "ON c.CLI_Codigo = dc.CLI_Codigo AND c.CLI_Activo = '1' " &
                                  "LEFT JOIN ProcedenciaCliente pc " &
                                  "ON pc.PRC_Codigo = c.PRC_Codigo AND PRC_Activo = '1' " &
                                  "WHERE dadpub.CIA+dadpub.SUC_Codigo+dadpub.ALM_Codigo+dadpub.BAN_Codigo = '" & CodigoBandeja & "' " &
                                  "AND dadpub.DADPUB_FechaFin IS NOT NULL AND dadpub.DADPUB_Activo = '1' " &
                                  "GROUP BY dcad.DCI_Codigo, pc.PRC_Descripcion"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Function obtenerCantidadItems(CodigoPedido As String) As String
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT COUNT(*) AS Contar " &
                                  "FROM Documento_Cliente_Almacen_Detalle dcad " &
                                  "INNER JOIN Documento_Almacen_Detalle dad  " &
                                  "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen AND dad.TDOC_Codigo = dcad.TDOC_Almacen " &
                                  "AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "WHERE dcad.DCI_Codigo = @DCI AND dcad.DCAD_Activo = '1'"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DCI", CodigoPedido)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds.Tables(0).Rows(0)("Contar").ToString.Trim
    End Function

    Function obtenerCantidadItemsLargos(codPedido As String, tipDocumento As String) As String
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT COUNT(*) AS Contar " &
                                  "FROM Documento_Cliente_Almacen_Detalle dcad " &
                                  "INNER JOIN VW_VistaDocumentoAlmacen da " &
                                  "ON da.CIA = dcad.CIA_Almacen AND da.SUC_Codigo = dcad.SUC_Almacen AND da.TDOC_Codigo = dcad.TDOC_Almacen " &
                                  "AND da.DCA_Codigo = dcad.DCA_Codigo AND da.DAD_Codigo = dcad.DAD_Codigo AND da.BAN_Codigo = '00000' " &
                                  "WHERE dcad.DCI_Codigo = @DCI AND dcad.TDOC_Cliente = @TDO"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DCI", codPedido)
                .Parameters.AddWithValue("@TDO", tipDocumento)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds.Tables(0).Rows(0)("Contar").ToString.Trim
    End Function

    Function obtenerDatosXBandejaBalanza(CodigoBandeja As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT dcad.DCI_Codigo AS Pedido, COUNT(*) AS CantItems " &
                                  "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                                  "ON dadpu.CIA = dadpub.CIA AND dadpu.SUC_Codigo = dadpub.SUC_Codigo " &
                                  "AND dadpu.DADPU_Codigo = dadpub.DADPU_Codigo AND dadpu.DADPU_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                                  "ON dadp.CIA = dadpu.CIA AND dadp.SUC_Codigo = dadpu.SUC_Codigo " &
                                  "AND dadp.TDOC_Codigo = dadpu.TDOC_Codigo " &
                                  "AND dadp.DCA_Codigo = dadpu.DCA_Codigo AND dadp.DADP_Codigo = dadpu.DADP_Codigo " &
                                  "AND dadp.DADP_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle dad " &
                                  "ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo " &
                                  "AND dad.TDOC_Codigo = dadp.TDOC_Codigo " &
                                  "AND dad.DCA_Codigo = dadp.DCA_Codigo " &
                                  "AND dad.DAD_Codigo = dadp.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                                  "ON dcad.CIA_Almacen = dad.CIA AND dcad.SUC_Almacen = dad.SUC " &
                                  "AND dcad.TDOC_Almacen = dad.TDOC_Codigo " &
                                  "AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo " &
                                  "AND dcad.DCAD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Detalle dcd " &
                                  "ON dcd.CIA = dcad.CIA_Cliente AND dcd.SUC = dcad.SUC_Cliente " &
                                  "AND dcd.TDOC_Codigo = dcad.TDOC_Cliente " &
                                  "AND dcd.DCI_Codigo = dcad.DCI_Codigo AND dcd.DCD_Codigo = dcad.DCD_Codigo " &
                                  "AND dcd.DCD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente dc " &
                                  "ON dc.CIA = dcd.CIA AND dc.SUC_Codigo = dcd.SUC " &
                                  "AND dc.TDOC_Codigo = dcd.TDOC_Codigo AND dc.DCI_Codigo = dcd.DCI_Codigo " &
                                  "WHERE dadpub.CIA+dadpub.SUC_Codigo+dadpub.ALM_Codigo+dadpub.BAN_Codigo = '" & CodigoBandeja & "' " &
                                  "AND dadpub.DADPUB_FechaFin IS NOT NULL AND dadpub.DADPUB_Activo = '1' AND DADPUB_Balanza IS NULL " &
                                  "GROUP BY dcad.DCI_Codigo"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Sub actualizarBandejaDADPUB(CodigoBalanza As String, CodigoBandeja As String, CodigoPedido As String)
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "UPDATE dadpub " &
                                  "SET dadpub.DADPUB_Balanza = @BAL " &
                                  "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                                  "INNER JOIN Bandeja b " &
                                  "ON b.BAN_Codigo = dadpub.BAN_Codigo AND b.BAN_Activo = '1' AND b.BAE_Estado = '02' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                                  "ON dadpu.DADPU_Codigo = dadpub.DADPU_Codigo AND dadpu.DADPU_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp  " &
                                  "ON dadp.DADP_Codigo = dadpu.DADP_Codigo AND dadp.DCA_Codigo = dadpu.DCA_Codigo AND dadp.DADP_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle dad " &
                                  "ON dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Codigo = dadp.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad  " &
                                  "ON dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo AND dcad.DCAD_Activo = '1' " &
                                  "AND dcad.DCI_Codigo = @PED " &
                                  "WHERE dadpub.CIA+dadpub.SUC_Codigo+dadpub.ALM_Codigo+dadpub.BAN_Codigo = @BAN " &
                                  "AND DADPUB_Activo = '1' AND DADPUB_FechaFin IS NOT NULL"

            Dim comando As New SqlCommand
            With comando
                .Connection = coneccion
                .CommandText = query
                .Parameters.AddWithValue("@BAL", CodigoBalanza)
                .Parameters.AddWithValue("@BAN", CodigoBandeja)
                .Parameters.AddWithValue("@PED", CodigoPedido)
                .ExecuteNonQuery()
            End With

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try
    End Sub

    Sub actualizarBandejaDADPUBC(CodigoBalanza As String, CodigoBandeja As String)
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "UPDATE Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja_Combo " &
                                  "SET DADPUBC_Balanza = @balanza " &
                                  "WHERE CIA+SUC_Codigo+ALM_Codigo+BAN_Codigo = @bandeja " &
                                  "AND DADPUBC_Activo = '1' AND DADPUBC_FechaFin IS NOT NULL"

            Dim comando As New SqlCommand
            With comando
                .Connection = coneccion
                .CommandText = query
                .Parameters.AddWithValue("@balanza", CodigoBalanza)
                .Parameters.AddWithValue("@bandeja", CodigoBandeja)
                .ExecuteNonQuery()
            End With

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try
    End Sub

    Function obtenerArticulosBandejaPedido(CodigoPedido As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            'Dim query As String = "SELECT dadp.DADP_Codigo,a.ART_Codigo,a.ART_Descripcion,a.ART_UM,dadpb.BAN_Codigo, " &
            '                      "(CASE " &
            '                            "WHEN ae.ART_Codigo IS NULL THEN dcd.DCD_Atendido " &
            '                            "WHEN ae.ART_Codigo IS NOT NULL THEN dcd.DCD_Atendido * ae.ARE_Cantidad " &
            '                      "END) AS Atendido, " &
            '                      "(dadpb.DADPB_Cantidad - SUM(ISNULL(dcbd.DBD_Cantidad,0))) AS NAtendido " &
            '                      "FROM Documento_Cliente dc " &
            '                      "INNER JOIN Documento_Cliente_Detalle dcd  " &
            '                      "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
            '                      "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
            '                      "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
            '                      "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC  " &
            '                      "AND dcad.TDOC_Cliente = dcd.TDOC_Codigo AND dcad.DCI_Codigo = dcd.DCI_Codigo " &
            '                      "AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcd.DCD_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle dad " &
            '                      "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen AND dad.TDOC_Codigo = dcad.TDOC_Almacen " &
            '                      "AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
            '                      "LEFT JOIN ArticuloEnsamblado ae " &
            '                      "ON ae.ART_Codigo = dcd.ART_Codigo AND ae.ART_Codigo_Combo = dad.ART_Codigo AND ae.ARE_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
            '                      "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
            '                      "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
            '                      "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo " &
            '                      "AND dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
            '                      "INNER JOIN Articulo a " &
            '                      "ON a.ART_Codigo = dadpu.ART_Codigo AND a.ART_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle_Pickeador_Bandeja dadpb " &
            '                      "ON dadpb.DADP_Codigo = dadp.DADP_Codigo AND dadpb.DADPB_FechaFin IS NOT NULL " &
            '                      "AND dadpb.DADPB_Activo = '1'  LEFT JOIN Documento_Cliente_Bulto_Detalle_DADPB tabla " &
            '                      "ON tabla.DADP_Codigo = dadpb.DADP_Codigo AND tabla.BAN_Codigo = dadpb.BAN_Codigo " &
            '                      "AND tabla.DCDB_Activo = '1' LEFT JOIN Documento_Cliente_Bulto_Detalle dcbd " &
            '                      "ON dcbd.CIA = tabla.CIA AND dcbd.SUC_Codigo = tabla.SUC_Codigo " &
            '                      "AND dcbd.TDOC_Codigo = tabla.TDOC_Codigo  AND dcbd.DCI_Codigo = tabla.DCI_Codigo " &
            '                      "AND dcbd.DCB_Codigo = tabla.DCB_Codigo AND dcbd.DBD_Codigo = tabla.DBD_Codigo " &
            '                      "AND dcbd.DBD_Activo = '1' " &
            '                      "WHERE dc.DCI_Codigo = @DCI AND dc.DCI_Estado = '07' " &
            '                      "GROUP BY dadp.DADP_Codigo,a.ART_Codigo,a.ART_Descripcion,a.ART_UM, " &
            '                      "dadpb.BAN_Codigo,dcd.DCD_Atendido,dadpb.DADPB_Cantidad,ae.ART_Codigo,ae.ARE_Cantidad " &
            '                      "HAVING (dadpb.DADPB_Cantidad - SUM(ISNULL(dcbd.DBD_Cantidad,0))) > 0 " &
            '                      "ORDER BY dadpb.BAN_Codigo"

            'Dim query As String = "SELECT dadp.DADP_Codigo,a.ART_Codigo,a.ART_Descripcion,a.ART_UM,dadpb.BAN_Codigo, " &
            '                      "(CASE " &
            '                          "WHEN ae.ART_Codigo IS NULL THEN dcd.DCD_Atendido " &
            '                           "WHEN ae.ART_Codigo IS NOT NULL THEN dcd.DCD_Atendido * ae.ARE_Cantidad " &
            '                      "END) AS Atendido, " &
            '                      "(CASE " &
            '                       "WHEN dadp.DADP_Cantidad%a.ART_CantLargo > 0 THEN ((dadp.DADP_Cantidad%a.ART_CantLargo) - SUM(ISNULL(dcbd.DBD_Cantidad,0))) " &
            '                       "ELSE ((dadpb.DADPB_Cantidad - (dadp.DADP_Cantidad%a.ART_CantLargo)) - SUM(ISNULL(dcbd.DBD_Cantidad,0))) END " &
            '                      ") AS NAtendido " &
            '                      "FROM Documento_Cliente dc " &
            '                      "INNER JOIN Documento_Cliente_Detalle dcd  " &
            '                      "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
            '                      "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
            '                      "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
            '                      "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC  " &
            '                      "AND dcad.TDOC_Cliente = dcd.TDOC_Codigo AND dcad.DCI_Codigo = dcd.DCI_Codigo " &
            '                      "AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcd.DCD_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle dad " &
            '                      "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen AND dad.TDOC_Codigo = dcad.TDOC_Almacen " &
            '                      "AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
            '                      "LEFT JOIN ArticuloEnsamblado ae " &
            '                      "ON ae.ART_Codigo = dcd.ART_Codigo AND ae.ART_Codigo_Combo = dad.ART_Codigo AND ae.ARE_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
            '                      "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
            '                      "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
            '                      "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo " &
            '                      "AND dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
            '                      "INNER JOIN Articulo a " &
            '                      "ON a.ART_Codigo = dadpu.ART_Codigo AND a.ART_Activo = '1' " &
            '                      "INNER JOIN Documento_Almacen_Detalle_Pickeador_Bandeja dadpb " &
            '                      "ON dadpb.DADP_Codigo = dadp.DADP_Codigo AND dadpb.DADPB_FechaFin IS NOT NULL " &
            '                      "AND dadpb.DADPB_Activo = '1'  LEFT JOIN Documento_Cliente_Bulto_Detalle_DADPB tabla " &
            '                      "ON tabla.DADP_Codigo = dadpb.DADP_Codigo AND tabla.BAN_Codigo = dadpb.BAN_Codigo " &
            '                      "AND tabla.DCDB_Activo = '1' LEFT JOIN Documento_Cliente_Bulto_Detalle dcbd " &
            '                      "ON dcbd.CIA = tabla.CIA AND dcbd.SUC_Codigo = tabla.SUC_Codigo " &
            '                      "AND dcbd.TDOC_Codigo = tabla.TDOC_Codigo  AND dcbd.DCI_Codigo = tabla.DCI_Codigo " &
            '                      "AND dcbd.DCB_Codigo = tabla.DCB_Codigo AND dcbd.DBD_Codigo = tabla.DBD_Codigo " &
            '                      "AND dcbd.DBD_Activo = '1' " &
            '                      "WHERE dc.DCI_Codigo = @DCI AND dc.DCI_Estado = '07' " &
            '                      "GROUP BY dadp.DADP_Codigo,a.ART_Codigo,a.ART_Descripcion,a.ART_UM,dadpb.BAN_Codigo, " &
            '                      "dcd.DCD_Atendido,dadpb.DADPB_Cantidad,ae.ART_Codigo,ae.ARE_Cantidad,dadp.DADP_Cantidad,a.ART_CantLargo " &
            '                      "HAVING (CASE " &
            '                                    "WHEN dadp.DADP_Cantidad%a.ART_CantLargo > 0 THEN ((dadp.DADP_Cantidad%a.ART_CantLargo) - SUM(ISNULL(dcbd.DBD_Cantidad,0))) " &
            '                                    "ELSE ((dadpb.DADPB_Cantidad - (dadp.DADP_Cantidad%a.ART_CantLargo)) - SUM(ISNULL(dcbd.DBD_Cantidad,0))) END ) > 0" &
            '                      "ORDER BY dadpb.BAN_Codigo"

            Dim query As String = "SELECT dadp.DADP_Codigo,a.ART_Codigo,a.ART_Descripcion,a.ART_UM,dadpb.BAN_Codigo, " &
                                  "dad.DAD_Cantidad AS Atendido, dadpb.DADPB_Cantidad - SUM(ISNULL(dcbd.DBD_Cantidad,0)) AS NAtendido " &
                                  "FROM Documento_Cliente dc " &
                                  "INNER JOIN Documento_Cliente_Detalle dcd  " &
                                  "ON dcd.CIA = dc.CIA AND dcd.SUC = dc.SUC_Codigo AND dcd.TDOC_Codigo = dc.TDOC_Codigo " &
                                  "AND dcd.DCI_Codigo = dc.DCI_Codigo AND dcd.DCD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                                  "ON dcad.CIA_Cliente = dcd.CIA AND dcad.SUC_Cliente = dcd.SUC AND dcad.TDOC_Cliente = dcd.TDOC_Codigo " &
                                  "AND dcad.DCI_Codigo = dcd.DCI_Codigo AND dcad.DCD_Codigo = dcd.DCD_Codigo AND dcd.DCD_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle dad " &
                                  "ON dad.CIA = dcad.CIA_Almacen AND dad.SUC = dcad.SUC_Almacen AND dad.TDOC_Codigo = dcad.TDOC_Almacen " &
                                  "AND dad.DCA_Codigo = dcad.DCA_Codigo AND dad.DAD_Codigo = dcad.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                                  "ON dadp.CIA = dad.CIA AND dadp.SUC_Codigo = dad.SUC AND dadp.TDOC_Codigo = dad.TDOC_Codigo " &
                                  "AND dadp.DCA_Codigo = dad.DCA_Codigo AND dadp.DAD_Codigo = dad.DAD_Codigo AND dadp.DADP_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                                  "ON dadpu.CIA = dadp.CIA AND dadpu.SUC_Codigo = dadp.SUC_Codigo AND dadpu.TDOC_Codigo = dadp.TDOC_Codigo " &
                                  "AND dadpu.DCA_Codigo = dadp.DCA_Codigo AND dadpu.DADP_Codigo = dadp.DADP_Codigo AND dadpu.DADPU_Activo = '1' " &
                                  "INNER JOIN Articulo a " &
                                  "ON a.ART_Codigo = dadpu.ART_Codigo AND a.ART_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Bandeja dadpb " &
                                  "ON dadpb.DADP_Codigo = dadp.DADP_Codigo AND dadpb.DADPB_FechaFin IS NOT NULL " &
                                  "AND dadpb.DADPB_Activo = '1' AND dadpb.BAN_Codigo <> '00000' " &
                                  "LEFT JOIN Documento_Cliente_Bulto_Detalle_DADPB tabla " &
                                  "ON tabla.DADP_Codigo = dadpb.DADP_Codigo AND tabla.BAN_Codigo = dadpb.BAN_Codigo " &
                                  "AND tabla.DCDB_Activo = '1' " &
                                  "LEFT JOIN Documento_Cliente_Bulto_Detalle dcbd " &
                                  "ON dcbd.CIA = tabla.CIA AND dcbd.SUC_Codigo = tabla.SUC_Codigo " &
                                  "AND dcbd.TDOC_Codigo = tabla.TDOC_Codigo  AND dcbd.DCI_Codigo = tabla.DCI_Codigo " &
                                  "AND dcbd.DCB_Codigo = tabla.DCB_Codigo AND dcbd.DBD_Codigo = tabla.DBD_Codigo AND dcbd.DBD_Activo = '1' " &
                                  "WHERE dc.TDOC_Codigo = 'P' AND dc.DCI_Codigo = @DCI AND dc.DCI_Estado = '07' " &
                                  "GROUP BY dadp.DADP_Codigo,a.ART_Codigo,a.ART_Descripcion,a.ART_UM,dadpb.BAN_Codigo,dad.DAD_Cantidad,dadpb.DADPB_Cantidad " &
                                  "HAVING(dadpb.DADPB_Cantidad - SUM(ISNULL(dcbd.DBD_Cantidad, 0)) > 0) " &
                                  "ORDER BY dadpb.BAN_Codigo"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@DCI", CodigoPedido)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

    Sub liberarBandejas(codBandeja As String)
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "UPDATE Bandeja " &
                                  "SET BAE_Estado = '01' " &
                                  "WHERE BAN_CIA+BAN_SUC+BAN_ALM+BAN_Codigo = @BAN " &
                                  "AND BAN_Activo = '1'"

            Dim comando As New SqlCommand
            With comando
                .Connection = coneccion
                .CommandText = query
                .Parameters.AddWithValue("@BAN", codBandeja)
                .ExecuteNonQuery()
            End With

        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try
    End Sub

    Function obtenerPedidoXBandeja(codBandeja As String) As String
        Dim texto As String = ""
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT DISTINCT(dcad.DCI_Codigo) AS Codigo " &
                                  "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                                  "INNER JOIN Bandeja b  " &
                                  "ON b.BAN_Codigo = dadpub.BAN_Codigo AND b.BAN_Activo = '1' AND b.BAE_Estado = '02' OR b.BAN_Codigo = 'SIS00' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                                  "ON dadpu.DADPU_Codigo = dadpub.DADPU_Codigo AND dadpu.DADPU_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                                  "ON dadp.CIA = dadpu.CIA AND dadp.SUC_Codigo = dadpu.SUC_Codigo " &
                                  "AND dadp.TDOC_Codigo = dadpu.TDOC_Codigo AND dadp.DADP_Codigo = dadpu.DADP_Codigo " &
                                  "AND dadp.DCA_Codigo = dadpu.DCA_Codigo AND dadp.DADP_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle dad " &
                                  "ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo AND dad.TDOC_Codigo = dadp.TDOC_Codigo " &
                                  "AND dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Codigo = dadp.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                                  "ON dcad.CIA_Almacen = dad.CIA AND dcad.SUC_Almacen = dad.SUC AND dcad.TDOC_Almacen = dad.TDOC_Codigo " &
                                  "AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo AND dcad.DCAD_Activo = '1' " &
                                  "LEFT JOIN Documento_Cliente_Balanza dcb " &
                                  "ON dcb.CIA = dcad.CIA_Cliente AND dcb.SUC_Codigo = dcad.SUC_Cliente AND dcb.TDOC_Codigo = dcad.TDOC_Cliente " &
                                  "AND dcb.DCI_Codigo = dcad.DCI_Codigo " &
                                  "WHERE dadpub.CIA+dadpub.SUC_Codigo+dadpub.ALM_Codigo+dadpub.BAN_Codigo = @BAN " &
                                  "AND dadpub.DADPUB_Activo = '1' AND dcb.DCB_FechaFin IS NULL"

            Dim comando As New SqlCommand(query, coneccion)
            With comando
                .Parameters.AddWithValue("@BAN", codBandeja)
            End With
            Dim adapter As New SqlDataAdapter(comando)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        If ds.Tables(0).Rows.Count <> 0 Then
            texto = ds.Tables(0).Rows(0)("Codigo").ToString.Trim
        End If

        Return texto
    End Function

    Function obtenerPedidosXBandeja(codigoBandeja As String) As DataSet
        Dim ds As New DataSet
        Dim conex As New Conexion
        Dim coneccion As New SqlConnection
        Try
            coneccion = conex.conectar()
            coneccion.Open()

            Dim query As String = "SELECT DISTINCT(DCI_Codigo) AS NroPedido " &
                                  "FROM Documento_Almacen_Detalle_Pickeador_Ubicacion_Bandeja dadpub " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador_Ubicacion dadpu " &
                                  "ON dadpu.CIA = dadpub.CIA AND dadpu.SUC_Codigo = dadpub.SUC_Codigo " &
                                  "AND dadpu.DADPU_Codigo = dadpub.DADPUB_Codigo AND dadpu.DADPU_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle_Pickeador dadp " &
                                  "ON dadp.CIA = dadpu.CIA AND dadp.SUC_Codigo = dadpu.SUC_Codigo " &
                                  "AND dadp.TDOC_Codigo = dadpu.TDOC_Codigo AND dadp.DCA_Codigo = dadpu.DCA_Codigo " &
                                  "AND dadp.DADP_Codigo = dadpu.DADP_Codigo AND dadp.DADP_Activo = '1' " &
                                  "INNER JOIN Documento_Almacen_Detalle dad " &
                                  "ON dad.CIA = dadp.CIA AND dad.SUC = dadp.SUC_Codigo AND dad.TDOC_Codigo = dadp.TDOC_Codigo " &
                                  "AND dad.DCA_Codigo = dadp.DCA_Codigo AND dad.DAD_Codigo = dadp.DAD_Codigo AND dad.DAD_Activo = '1' " &
                                  "INNER JOIN Documento_Cliente_Almacen_Detalle dcad " &
                                  "ON dcad.CIA_Almacen = dad.CIA AND dcad.SUC_Almacen = dad.SUC " &
                                  "AND dcad.TDOC_Almacen = dad.TDOC_Codigo AND dcad.DAD_Codigo = dad.DAD_Codigo " &
                                  "AND dcad.DCA_Codigo = dad.DCA_Codigo AND dcad.DCAD_Activo = '1' " &
                                  "WHERE dadpub.CIA + dadpub.SUC_Codigo + dadpub.ALM_Codigo + dadpub.BAN_Codigo = '" & codigoBandeja & "'"

            Dim adapter As New SqlDataAdapter(query, coneccion)
            adapter.Fill(ds)
        Catch ex As SqlException
            MsgBox("La conexión no está disponible.")
        Catch e As Exception
            MsgBox(e.Message)
        Finally
            coneccion.Close()
        End Try

        Return ds
    End Function

End Class
